import json
import sys
import os

sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from project_utils.mysql_conncet import ReadFromMysql
from config import *

mysql = ReadFromMysql(host=mysqlhost, user=user, password=password, port=3306)
mysql.makepool(database)


# def mappings():
#     conn = mysql.getconn()
#     sc = conn.cursor()
#     column = ["original_field", "natrual_field", "data_type"]
#
#     sql = f""" SELECT original_field,natrual_field, data_type
#             FROM {hbase_map_table}
#             where dy_code='washer'  """
#
#     sc.execute(sql)
#     conn.commit()
#     result = sc.fetchall()
#     result = [dict(zip(column, i)) for i in result]
#     return result


def match_mappings(tablename):
    conn = mysql.getconn()
    sc = conn.cursor()
    column = mysql.column_from_mysql(tablename)
    sc.execute(f"""SELECT
                        mm.* 
                    FROM
                        material m
                    LEFT JOIN {tablename} mm 
                    ON m.`name` = mm.material
                    where mm.id is not null
                    and mm.dy_code='washer'
                        """)
    conn.commit()
    result = sc.fetchall()
    mappings = {}
    dc = [dict(zip(column, i)) for i in result]
    for i in dc:
        model = i.get('material', 'general')
        category = i.get('category', 'general')
        original_field = i.get('original_field')
        natrual_field = i.get('natrual_field')
        data_type = i.get('data_type')
        if not mappings.get(model):
            mappings[model] = {}
        if not mappings[model].get(category):
            mappings[model][category] = {}
        if not mappings[model][category].get(original_field):
            mappings[model][category][original_field] = {}

        mappings[model][category][original_field]["value"] = natrual_field
        mappings[model][category][original_field]["data_type"] = data_type
    return mappings


def not_match_mappings(tablename):
    conn = mysql.getconn()
    sc = conn.cursor()
    column = ['material', 'category', 'original_field', 'natrual_field', 'data_type']
    sc.execute(f"""select 
                        t1.`name`, t2.category, t2.original_field, t2.natrual_field, t2.data_type
                    from
                    (
                    SELECT
                            m.name, 'general' as model
                    FROM
                            material m
                    LEFT JOIN {tablename} mm 
                    ON m.`name` = mm.material
                    where mm.id is null
                    and m.type='WASHER'
                    ) t1
                    left join 
                    {tablename} t2
                    on t1.model=t2.model
                    where t2.model is not null
                    and t2.dy_code='washer'""")
    conn.commit()
    result = sc.fetchall()
    mappings = {}
    dc = [dict(zip(column, i)) for i in result]
    for i in dc:
        model = i.get('material', 'general')
        category = i.get('category', 'general')
        original_field = i.get('original_field')
        natrual_field = i.get('natrual_field')
        data_type = i.get('data_type')
        if not mappings.get(model):
            mappings[model] = {}
        if not mappings[model].get(category):
            mappings[model][category] = {}
        if not mappings[model][category].get(original_field):
            mappings[model][category][original_field] = {}

        mappings[model][category][original_field]["value"] = natrual_field
        mappings[model][category][original_field]["data_type"] = data_type
    return mappings


def get_duplicate(maps):
    """
    :param maps: 每个型号的mapings；dict格式
    :return: 一一对应的dict, 2对1的dict (2个dict)
    """
    model_map = {}
    duplicate_result = {}
    only_result = {}
    duplicate_key = []
    only_key = []
    if maps:
        for key in maps:
            value = maps[key]["value"]
            num = model_map.get(value)
            if num:
                model_map[value] += 1
            else:
                model_map[value] = 1

    for key in model_map:
        model_zip = zip([i for i in maps], [maps[i]["value"] for i in maps])
        for i, j in model_zip:
            if (model_map[key] >= 2) & (j == key):
                duplicate_key.append(i)
            elif (model_map[key] < 2) & (j == key):
                only_key.append(i)

    for key in only_key:
        only_result[key] = maps[key]
    for key in duplicate_key:
        natrual_field = duplicate_result.get(maps[key]["value"])
        if not natrual_field:
            duplicate_result[maps[key]["value"]] = {}
            duplicate_result[maps[key]["value"]]["value1"] = key
            duplicate_result[maps[key]["value"]]["data_type"] = maps[key]["data_type"]
        else:
            duplicate_result[maps[key]["value"]]["value2"] = key

    return only_result, duplicate_result


def merge_mapping(mapping):
    result = {}
    ms = {}
    for material in mapping:
        for category in mapping[material]:
            s = json.dumps(mapping[material][category])
            if not result.get(s):
                result[s] = {}
            if not result[s].get(category):
                result[s][category] = []
                result[s][category].append([])
            if len(result[s][category][-1]) >= 99:
                result[s][category].append([])
            result[s][category][-1].append(material)
            ms[material] = ''
    return result, ms
